* 00_BHP_data from Values
cap log close
log using "$log/00_BHP_data", text replace

clear
gen year = .
forv y = 1998 / 2018 {
append using "$data\bhp_7519_m06_v2_`y'", keep(ao_bula ao_kreis betnr)
replace year = `y' if missing(year)
}

rename year jahr
merge 1:1 jahr betnr using "$data\bhp_7519_m06_wgen_v2.dta", keepusing(w08_3_gen)
keep if _merge == 3
drop _merge
rename jahr year 

replace w08_3_gen = . if missing(w08_3_gen)
replace ao_bula = . if missing(ao_bula)
replace ao_kreis = . if missing(ao_kreis)

sort betnr year
replace w08_3_gen = w08_3_gen[_n-1] if missing(w08_3_gen) & !missing(w08_3_gen[_n-1]) & betnr == betnr[_n-1]
replace ao_bula = ao_bula[_n-1] if missing(ao_bula) & !missing(ao_bula[_n-1]) & betnr == betnr[_n-1]
replace ao_kreis = ao_kreis[_n-1] if missing(ao_kreis) & !missing(ao_kreis[_n-1]) & betnr == betnr[_n-1]
gsort betnr -year
replace w08_3_gen = w08_3_gen[_n-1] if missing(w08_3_gen) & !missing(w08_3_gen[_n-1]) & betnr == betnr[_n-1]
replace ao_bula = ao_bula[_n-1] if missing(ao_bula) & !missing(ao_bula[_n-1]) & betnr == betnr[_n-1]
replace ao_kreis = ao_kreis[_n-1] if missing(ao_kreis) & !missing(ao_kreis[_n-1]) & betnr == betnr[_n-1]

*Assign one industry to each firm
bysort betnr: egen ind = mode(w08_3_gen), maxmode
bysort betnr: egen bula = mode(ao_bula), maxmode
bysort betnr: egen kreis = mode(ao_kreis), maxmode

drop w08_3_gen ao_bula year ao_kreis
bysort betnr: gen n = _n
keep if n==1
drop n

*Assign 3 digit industry to sector
gen sector = ""
replace sector = "A: Agriculture, forestry and fishing" if ind>=10 & ind<50
replace sector = "B: Mining and quarrying" if ind>=50 & ind<100
replace sector = "C: Manufacturing" if ind>=100 & ind<350
replace sector = "D: Electricity, gas, steam and air conditioning supply" if ind>=350 & ind<360
replace sector = "E: Water supply; sewerage, waste management and remediation activities" if ind>=360 & ind<410
replace sector = "F: Construction" if ind>=410 & ind<450
replace sector = "G: Wholesale and retail trade; repair of motor vehicles and motocycles" if ind>=450 & ind<490
replace sector = "H: Transportation and storage" if ind>=490 & ind<550
replace sector = "I: Accomodation and food service activities" if ind>=550 & ind<580
replace sector = "J: Information and communication" if ind>=580 & ind<640
replace sector = "K: Financial and insurance activities" if ind>=640 & ind<680
replace sector = "L: Real estate activities" if ind>=680 & ind<690
replace sector = "M: Professional, scientific and technical activities" if ind>=690 & ind<770
replace sector = "N-78.*: Administrative and support service activities" if ind>=770 & ind<840
replace sector = "78.*: Employment activities (normally part of N)" if ind>=780 & ind<790
replace sector = "O: Public administration and defense; compulsory social security" if ind>=840 & ind<850
replace sector = "P: Education" if ind>=850 & ind<860
replace sector = "Q: Human health and social work activities" if ind>=860 & ind<900
replace sector = "R: Arts, entertainment and recreation" if ind>=900 & ind<940
replace sector = "S: Other service activities" if ind>=940 & ind<970
replace sector = "T: Private households" if ind>=970 & ind<990
replace sector = "U: Activities of extraterritorial organizations and bodies" if ind>=990 & !missing(ind)
replace sector = "Missing sector" if missing(ind)

*Keep betnr and sector for merging
keep betnr sector ind bula kreis


compress
save "$temp/wz.dta", replace
